﻿'Description:例行试验类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh 
'create Date:20190626
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO

Public Class uLMSRoutineTestBusiness
    Implements uLMSRoutineTestIBusiness
    ''' <summary>
    ''' 获取例行试验部件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRoutineTestInfo(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSRoutineTestIBusiness.GetRoutineTestInfo
        Dim result As New DataSet
        Dim strSql As String = ""
        Dim productName As String = ""



        '查询发动机件号工艺信息
        strSql = <string>
                     SELECT pb.productname,ws.workflowstepname,substr(ws.workflowstepname,INSTR(ws.workflowstepname,'-')+1) opdesc,ws.workflowstepid,w.workflowid,wb.workflowname,w.workflowrevision FROM product p--60000
                    LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                    LEFT JOIN workflow w ON w.workflowid=p.workflowid
                    LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                    LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                 </string>.Value
        strSql += String.Format(" WHERE  w.factoryid ='{0}' AND p.productid='{1}' ", para("FactoryID"), para("ProductID"))
        strSql += " order by ws.sequence asc"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Then
            Return result
            Exit Function
        End If
        productName = dt.Rows(0)("productname").ToString
        '查询所有大阶段下的部件和工艺（含有例行试验）
        strSql = <string>
                    SELECT w.phasenode,w.workflowid,wb.workflowname,w.workflowrevision,pb.productname,p.productrevision,p.description,w.description WorkflowDesc  FROM workflow w--部件工艺
                    LEFT JOIN Workflowbase wb ON wb.revofrcdid=w.workflowid 
                    LEFT JOIN productworkflowmapping pw ON pw.workflowid=w.workflowid
                    LEFT JOIN product p ON p.productid=pw.productid
                    LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                    LEFT JOIN Workflowstep ws ON ws.workflowid=w.workflowid--部件工序
                       LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                    LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                    LEFT JOIN spectype st ON st.spectypeid=s.spectypeid
                    WHERE st.spectypename='例行试验' and   wb.workflowname IS NOT NULL 
                </string>
        strSql += String.Format(" AND  w.factoryid ='{0}' ", para("FactoryID"))
        Dim strCondition As String = ""
        Dim dt2 As New DataTable '所有部件工艺
        Dim opdescList As New List(Of String)
        For Each row As DataRow In dt.Rows
            If row.IsNull("opdesc") Or opdescList.Contains(row("opdesc").ToString) Then
                Continue For
            End If
            strCondition = String.Format(" AND(w.phasenode LIKE '%;{0};%' OR  w.phasenode LIKE '{0};%' OR w.phasenode LIKE '%;{0}' OR w.phasenode='{0}')",
                                         row("opdesc").ToString)

            strCondition = String.Format(" AND(w.ly2modle_node LIKE '%;{0};%' OR  w.ly2modle_node LIKE '{0};%' OR w.ly2modle_node LIKE '%;{0}' OR w.ly2modle_node='{0}')",
                                       row("productname").ToString)
            ' strCondition += String.Format(" and instr(wf.ly2modle_node,'{0}')>0 ", row("productname").ToString)
            Dim _dt2 As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql + strCondition)
            dt2.Merge(_dt2)
            opdescList.Add(row("opdesc").ToString)
        Next

        dt2.AcceptChanges()
        dt2.TableName = "OriOptionalInfo"

        '筛选mbomtable_tmp表中必须有值
        strSql = String.Format(<string>
                      SELECT MATNR,IDNRK FROM mbomtable_tmp m WHERE MATNR!= IDNRK
                     start with m.matnr = '{0}' 
                     connect by nocycle prior m.idnrk= m.matnr 
                 </string>.Value, productName) '根据发动机件号查询BOM
        Dim engineBomDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql) '发动机整机BOM
        Dim dt3 As DataTable = dt2.Clone  '可选工艺
        dt3.TableName = "OptionalInfo"
        Dim dt4 As New DataTable  '已选工艺
        dt4.TableName = "OriSelectInfo"
        Dim dt4_show As DataTable = dt2.Clone  '已选工艺，将展示在页面的
        dt4_show.TableName = "SelectInfo"
        strSql = String.Format(<string>
                      SELECT *  FROM ROUTINETESTRECORD t WHERE t.engineproductname='{0}'                      
                 </string>.Value, productName) '根据发动机件号查询已选的例行试验工艺

        If para.Keys.Contains("RoutineTestType") Then
            strSql += String.Format(" and RoutineTestType='{0}'", para("RoutineTestType"))
        End If
        strSql += " ORDER BY t.createdate ASC"
        dt4 = DatabaseHelper.Query_ReturnDatTable(strSql)
        For Each row As DataRow In dt2.Rows
            If productName = row("productname").ToString Or engineBomDt.Select("IDNRK='" + row("productname").ToString + "'").Length > 0 Then
                If dt4.Select(String.Format("phasenode='{0}' and PARTSWORKFLOWNAME='{1}'", row("phasenode").ToString, row("workflowname").ToString)).Length = 0 Then
                    dt3.ImportRow(row)
                Else
                    dt4_show.ImportRow(row)
                End If
            End If
        Next
        ' dt3 = dt3.DefaultView.ToTable(True, "phasenode", "workflowid", "workflowname", "workflowrevision", "productname", "productrevision", "description", "WorkflowDesc")
        dt3 = dt3.DefaultView.ToTable(True, "phasenode", "workflowname", "productname", "description", "WorkflowDesc")
        dt3.AcceptChanges()

        'dt4_show = dt4_show.DefaultView.ToTable(True, "phasenode", "workflowid", "workflowname", "workflowrevision", "productname", "productrevision", "description", "WorkflowDesc")
        dt4_show = dt4_show.DefaultView.ToTable(True, "phasenode", "workflowname", "productname", "description", "WorkflowDesc")
        dt4_show.AcceptChanges()
        '
        dt4.TableName = "OriSelectInfo"
        result.Tables.Add(dt2)
        result.Tables.Add(dt3)
        result.Tables.Add(dt4)
        result.Tables.Add(dt4_show)

        Return result
    End Function

    ''' <summary>
    ''' 保存例行试验维护数据，更新，删除，插入
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveGetRoutineTest(ByVal dt As DataTable) As Boolean Implements uLMSRoutineTestIBusiness.SaveGetRoutineTest
        SaveGetRoutineTest = False

        Dim updateSql As String = <string> UPDATE ROUTINETESTRECORD t SET t.routinetesttype='{0}' ,t.routinetesttypedesc='{1}' 
        WHERE t.engineproductname='{2}'  AND t.phasenode='{3}' AND t.partsworkflowname='{4}' and t.routinetesttype='{5}'</string>.Value

        Dim insertSql As String = <string>INSERT INTO  ROUTINETESTRECORD t (t.routinetestrecordid,t.engineproductname,t.routinetesttype,t.routinetesttypedesc,t.phasenode,t.partsworkflowname,
        t.createemployee,t.createdate) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',to_date('{7}','yyyy/mm/dd hh24:mi:ss'))</string>.Value

        Dim deleteSql As String = <String>DELETE ROUTINETESTRECORD t WHERE t.engineproductname='{0}' AND t.phasenode='{1}' AND t.partsworkflowname='{2}' and  t.routinetesttype='{3}'</String>

        Dim strSqlList As New ArrayList
        For Each row As DataRow In dt.Rows
            Dim strSql As String = ""
            If row.IsNull("needDeal") Then '删除
                strSql = String.Format(deleteSql, row("engineproductname").ToString, row("phasenode").ToString, row("partsworkflowname").ToString, row("routinetesttype").ToString)
            ElseIf row("needDeal") = 1 Then '更新
                strSql = String.Format(updateSql, row("routinetesttype").ToString, row("routinetesttypedesc").ToString, row("engineproductname").ToString, row("phasenode").ToString,
                                       row("partsworkflowname").ToString, row("routinetesttype").ToString)
            ElseIf row("needDeal") = 2 Then '插入
                strSql = String.Format(insertSql, row("routinetestrecordid").ToString, row("engineproductname").ToString, row("routinetesttype").ToString, row("routinetesttypedesc").ToString,
                                       row("phasenode").ToString, row("partsworkflowname").ToString, row("createemployee").ToString, row("createdate").ToString)
            Else
                Continue For
            End If
            strSqlList.Add(strSql)
        Next
        DatabaseHelper.ExecuteSqlTran(strSqlList)
        SaveGetRoutineTest = True
    End Function

    ''' <summary>
    ''' 根据发动机物料号，查询机型及例行试验信息
    ''' </summary>
    ''' <param name="engineProductName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPfndRoutineInfo(ByVal engineProductName As String) As DataTable Implements uLMSRoutineTestIBusiness.GetPfndRoutineInfo
        Dim strSql As String = <sitrng>
          SELECT pf.productfamilyname,rt.*  FROM ROUTINETESTRECORD rt
          LEFT JOIN productbase pb ON pb.productname=rt.engineproductname
          LEFT JOIN product p ON p.productid=pb.revofrcdid
          LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid=p.workflowid
          LEFT JOIN productfamily pf ON pf.productfamilyid=wpf.productfamilylistid</sitrng>.Value

        strSql += String.Format(" where pb.productname='{0}'", engineProductName)

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取部件例行试验信息，根据发动机件号……
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPartsRoutineTest(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSRoutineTestIBusiness.GetPartsRoutineTest
        Dim strSql As String = <string>SELECT rt.phasenode,pb.productname,p.description,wb.workflowname,w.description WorkflowDesc,ws.workflowstepname  FROM ROUTINETESTRECORD rt
        LEFT JOIN workflowbase wb ON wb.workflowname=rt.partsworkflowname
        LEFT JOIN workflow w ON w.workflowid=wb.revofrcdid
        LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid --AND ws.workflowstepname LIKE '%例行试验%'
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
LEFT JOIN spectype st ON st.spectypeid=s.spectypeid
        LEFT JOIN productworkflowmapping pw ON pw.workflowid=w.workflowid
        LEFT JOIN product p ON p.productid=pw.productid
        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid where st.spectypename='例行试验'</string>.Value

        strSql += String.Format(" and rt.engineproductname ='{0}'", para("EngineProductName"))

        If para.Keys.Contains("RoutineTestType") Then
            strSql += String.Format(" and rt.RoutineTestType ='{0}'", para("RoutineTestType"))
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 保存例行试验计划数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveRoutineTestPlan(ByVal para As Dictionary(Of String, String), ByRef strMsg As String) As Boolean Implements uLMSRoutineTestIBusiness.SaveRoutineTestPlan
        SaveRoutineTestPlan = False
        Dim selectSql As String = " SELECT * FROM ROUTINETESTRECORDplan t WHERE t.ENGINENO='{0}'  AND t.ROUTINETESTRECORDPLANYEAR={1}"

        Dim updateSql As String = " UPDATE ROUTINETESTRECORDplan t SET t.ENGINEPRODUCTNAME='{0}',t.ROUTINETESTRECORDPLANNAME='{1}',t.ROUTINETESTTYPE='{2}' WHERE t.ENGINENO='{3}'  AND t.ROUTINETESTRECORDPLANYEAR='{4}'"

        Dim insertSql As String = <String> INSERT INTO ROUTINETESTRECORDplan t (t.routinetestrecordplanid,t.routinetestrecordplanname,t.routinetestrecordplanyear,t.createdate,t.createemployeeid,t.engineproductname,t.routinetesttype,t.engineno)
        VALUES('{0}','{1}',{2},SYSDATE,'{3}','{4}','{5}','{6}')</String>.Value

        Dim selectDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(selectSql, para("EngineNo"), para("RoutineTestPlanYear")))

        If selectDt.Rows.Count > 0 Then
            strMsg = "发动机号" + para("EngineNo") + "," + para("RoutineTestPlanYear") + "已有例行试验记录"
            Exit Function
        ElseIf selectDt.Rows.Count = 1 Then
            DatabaseHelper.ExecuteSql(String.Format(updateSql, para("EngineProductName"), para("RoutineTestPlanName"), para("RoutineTestType"), para("EngineNo"), para("RoutineTestPlanYear")))
        Else
            DatabaseHelper.ExecuteSql(String.Format(insertSql, Guid.NewGuid.ToString, para("RoutineTestPlanName"), para("RoutineTestPlanYear"), para("EmployeeID"),
                                              para("EngineProductName"), para("RoutineTestType"), para("EngineNo")))
        End If

        SaveRoutineTestPlan = True
    End Function
    ''' <summary>
    ''' 获取例行试验计划弹出框数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRoutineTestPlanPupInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSRoutineTestIBusiness.GetRoutineTestPlanPupInfo
        Dim strSql As String = <string>  SELECT t.routinetestrecordplanname,e.fullname,to_char(t.createdate,'yyyy/mm/dd') createdate,COUNT(t.engineno) ENGINENoCount  FROM ROUTINETESTRECORDplan t
        LEFT JOIN employee e ON e.employeeid=t.createemployeeid where 1=1</string>.Value

        If para.Keys.Contains("ROUTINETESTRECORDPLANNAME") Then
            strSql += String.Format(" and t.ROUTINETESTRECORDPLANNAME like '%{0}%'", para("ROUTINETESTRECORDPLANNAME"))
        End If

        If para.Keys.Contains("ROUTINETESTRECORDPLANYEAR") Then
            strSql += String.Format(" and t.ROUTINETESTRECORDPLANYEAR like '%{0}%'", para("ROUTINETESTRECORDPLANYEAR"))
        End If

        strSql += " group by t.routinetestrecordplanname,e.fullname,to_char(t.createdate,'yyyy/mm/dd')"
        strSql += " order by  to_char(t.createdate,'yyyy/mm/dd') asc"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 查询发动机号是否有分解记录
    ''' </summary>
    ''' <param name="engineNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsHaveEnginepartplan(ByVal engineNo As String) As Boolean Implements uLMSRoutineTestIBusiness.IsHaveEnginepartplan
        IsHaveEnginepartplan = False
        Dim strSql As String = <string>SELECT ep.enginepartplanid FROM enginepartplan ep
        LEFT JOIN Userattribute ut ON ut.parentid=ep.mfgorderid AND ut.userattributename='发动机'
        WHERE ROWNUM=1  
         </string>.Value
        strSql += String.Format(" AND ut.attributevalue='{0}'", engineNo)
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            IsHaveEnginepartplan = True
        End If
    End Function

    ''' <summary>
    ''' 获取例行试验和维护信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRoutineTestPlanInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSRoutineTestIBusiness.GetRoutineTestPlanInfo
        Dim strSql As String = <string>SELECT r.routinetestrecordid,r.routinetesttypedesc,
                                   r.phasenode,r.partsworkflowname, rp.*  FROM ROUTINETESTRECORDplan rp
                            LEFT JOIN ROUTINETESTRECORD r ON r.engineproductname=rp.engineproductname AND r.routinetesttype=rp.routinetesttype
                            WHERE 1=1 </string>
        If para.Keys.Contains("EngineNo") Then '发动机号
            strSql += String.Format(" and rp.EngineNo='{0}'", para("EngineNo"))
        End If
        If para.Keys.Contains("Phasenode") Then '大阶段
            strSql += String.Format(" and r.Phasenode='{0}'", para("Phasenode"))
        End If
        If para.Keys.Contains("RoutineTestPlanYear") Then '年份
            strSql += String.Format(" and rp.Routinetestrecordplanyear={0}", para("RoutineTestPlanYear"))
        End If
        If para.Keys.Contains("PartsWorkflowName") Then '部件工艺
            strSql += String.Format(" and r.partsworkflowname='{0}'", para("PartsWorkflowName"))
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 导入标准样件
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function LeadInETALONBASEDATA(ByVal dt As DataTable) As Boolean Implements uLMSRoutineTestIBusiness.LeadInETALONBASEDATA
        LeadInETALONBASEDATA = False
        Dim selectSql As String = <String>SELECT *  FROM ETALONBASEDATA t WHERE t.etalonbasedatano='{0}' AND t.productfamily='{1}'</String>
        Dim updateSql As String = <String>UPDATE ETALONBASEDATA t SET t.productname='{0}',t.productdesc='{1}',t.etaloncontent='{2}',t.stepinfo='{3}',t.begindate=to_date('{4}','yyyy/mm/dd'),t.finishdate=to_date('{5}','yyyy/mm/dd'),t.etalontype='{6}' WHERE t.etalonbasedatano='{7}'
        AND t.productfamily='{8}'</String>
        Dim insertSql As String = <String>INSERT INTO ETALONBASEDATA t (t.etalonbasedataid,t.etalonbasedatano,t.productname,t.productdesc,t.productfamily,t.etaloncontent,t.stepinfo,t.begindate,t.finishdate,t.etalontype)
        VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',to_date('{7}','yyyy/mm/dd'),to_date('{8}','yyyy/mm/dd'),'{9}')</String>
        Dim sqlList As New ArrayList
        For Each row As DataRow In dt.Rows
            Dim sql As String = ""
            sql = String.Format(selectSql, row("etalonbasedatano").ToString, row("productfamily").ToString)
            Dim selectDt As DataTable = DatabaseHelper.Query_ReturnDatTable(sql)
            sql = ""
            If selectDt.Rows.Count > 0 Then
                sql = String.Format(updateSql, row("productname").ToString, row("productdesc").ToString, row("etaloncontent").ToString, row("stepinfo").ToString, row("begindate").ToString.Replace("0:00:00", ""), row("finishdate").ToString.Replace("0:00:00", ""), row("etalontype").ToString,
                                    row("etalonbasedatano").ToString, row("productfamily").ToString)
            Else
                sql = String.Format(insertSql, Guid.NewGuid.ToString, row("etalonbasedatano").ToString, row("productname").ToString, row("productdesc").ToString, row("productfamily").ToString,
                                    row("etaloncontent").ToString, row("stepinfo").ToString, row("begindate").ToString.Replace("0:00:00", ""), row("finishdate").ToString.Replace("0:00:00", ""), row("etalontype").ToString)
            End If
            sqlList.Add(sql)
        Next
        DatabaseHelper.ExecuteSqlTran(sqlList)
        LeadInETALONBASEDATA = True
    End Function

    ''' <summary>
    ''' 保存技术通知单表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveTechnicalNoticeInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSRoutineTestIBusiness.SaveTechnicalNoticeInfo
        SaveTechnicalNoticeInfo = False
        Dim selectSql As String = "SELECT *  FROM TechnicalNoticeInfo t WHERE t.workflowname='{0}'"
        Dim deleteSql As String = "delete TechnicalNoticeInfo t where 1=1"
        Dim insertSql As String = <string>INSERT INTO TechnicalNoticeInfo t (TechnicalNoticeInfoid,workflowname,engineNo,begindate,finishDate,noticedate,createemployeeid,
        createdate)VALUES('{0}','{1}','{2}',to_date('{3}','yyyy/mm/dd'),to_date('{4}','yyyy/mm/dd'),to_date('{5}','yyyy/mm/dd hh24:mi:ss'),'{6}',SYSDATE)</string>.Value
        Dim workflowName As String = para("WorkflowName")


        Dim sql As String = deleteSql + " and workflowname='" + workflowName + "'"
        DatabaseHelper.ExecuteSql(sql)
        If String.IsNullOrWhiteSpace(para("EngineNo")) Then
            sql = String.Format(insertSql, Guid.NewGuid.ToString, workflowName, "", para("BeginDate"), para("FinishDate"), para("NoticeDate"), para("EmployeeID"))
            DatabaseHelper.ExecuteSql(sql)
        Else
            For Each engineNo As String In para("EngineNo").Split(";")
                If String.IsNullOrWhiteSpace(engineNo) Then
                    Continue For
                End If
                sql = String.Format(insertSql, Guid.NewGuid.ToString, workflowName, engineNo, para("BeginDate"), para("FinishDate"), para("NoticeDate"), para("EmployeeID"))
                DatabaseHelper.ExecuteSql(sql)
            Next
        End If
        SaveTechnicalNoticeInfo = True
    End Function

    ''' <summary>
    ''' 查询技通单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTechnicalWorkflow(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements uLMSRoutineTestIBusiness.GetTechnicalWorkflow
        Dim strSQL As String = <string>
               SELECT wb.workflowname,w.workflowrevision,w.workflowid,REPLACE(wm_concat(DISTINCT tn.engineno),',',';') engineno,w.notes,tn.begindate,tn.finishdate,tn.noticedate,COUNT(csd.containerstepdispatchid) dispatchNum,
 COUNT(eg.enginepartplanid) enginepartplanNum  FROM workflow w
  LEFT JOIN Workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
  LEFT JOIN TechnicalNoticeInfo tn ON tn.workflowname=wb.workflowname
  LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
    LEFT JOIN containerstepdispatch csd ON csd.workflowstepid=ws.workflowstepid
  LEFT JOIN enginepartplan eg ON eg.workflowid=w.workflowid
  WHERE w.workflowtype='施工工艺'  and tn.TechnicalNoticeInfoid is not null 
                              </string>
        If para.ContainsKey("Status") Then
            strSQL += String.Format(" and w.status={0}", para("Status"))
        End If
        If para.ContainsKey("WorkflowName") Then
            strSQL += String.Format(" and wb.workflowname LIKE '%{0}%' ", para("WorkflowName"))
        End If
        strSQL += "  GROUP BY wb.workflowname,w.workflowrevision,w.workflowid,w.notes,tn.begindate,tn.finishdate,tn.noticedate "
        'Modify by Yuxj20180906
        If para.ContainsKey("EngineNO") Then
            If Not String.IsNullOrWhiteSpace(para("EngineNO")) Then
                strSQL += String.Format(" HAVING wm_concat(tn.engineno) LIKE '%{0}%' ", para("EngineNO"))
            End If
        End If


        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSQL.ToString
        '主数据进行分页
        GetTechnicalWorkflow = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 查询技通单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTechnicalWorkflowForSplit(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements uLMSRoutineTestIBusiness.GetTechnicalWorkflowForSplit
        Dim strSQL As String = String.Format(<string>SELECT wb.workflowname,w.workflowrevision,w.workflowid,tn.engineno,w.notes,tn.begindate,tn.finishdate,tn.noticedate,COUNT(en.enginepartplanid) enginepartplanNum FROM workflow w
LEFT JOIN Workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN TechnicalNoticeInfo tn ON tn.workflowname=wb.workflowname
LEFT JOIN enginepartplan en ON en.workflowid=w.workflowid AND en.mfgorderid='{0}'
WHERE w.workflowtype='施工工艺' AND tn.technicalnoticeinfoid IS NOT NULL and w.status=1</string>.Value, para("MfgorderID"))

        If para.ContainsKey("WorkflowName") Then
            strSQL += String.Format(" and wb.workflowname LIKE '%{0}%' ", para("WorkflowName"))
        End If

        'Modify by Yuxj20180906
        If para.ContainsKey("EngineNO") Then
            strSQL += String.Format(" and (tn.engineno='{0}' or tn.engineno is null) ", para("EngineNO"))
        End If
        strSQL += "  GROUP BY wb.workflowname,w.workflowrevision,w.workflowid,w.notes,tn.begindate,tn.finishdate,tn.noticedate,tn.engineno "
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSQL.ToString
        '主数据进行分页
        GetTechnicalWorkflowForSplit = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取订单及分解计划信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgorderEnginePartPlanInfo(ByVal mfgorderid As String, ByVal workflowid As String) As DataTable Implements uLMSRoutineTestIBusiness.GetMfgorderEnginePartPlanInfo
        Dim strSql As String = String.Format(<string>SELECT en.enginepartplanid  FROM mfgorder m
        LEFT JOIN enginepartplan en ON en.mfgorderid=m.mfgorderid 
        WHERE m.mfgorderid='{0}' AND en.workflowid='{1}' </string>.Value, mfgorderid, workflowid)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 查询考核项信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAppraisalItem(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements uLMSRoutineTestIBusiness.GetAppraisalItem
        Dim strSQL As String = <string>
        SELECT WC.*  FROM WORKCENTEREXAMINEINFO WC
        
                              </string>
        If para.ContainsKey("FactoryID") Then
            strSQL += String.Format(" where  wc.FactoryID = '{0}' ", para("FactoryID"))
        End If
        If para.ContainsKey("ProductNO") Then
            strSQL += String.Format(" and wc.productname LIKE '%{0}%' ", para("ProductNO"))
        End If
        If para.ContainsKey("ContainerNo") Then
            strSQL += String.Format(" and wc.batch LIKE '%{0}%' ", para("ContainerNo"))
        End If
        If para.ContainsKey("SpecNo") Then
            strSQL += String.Format(" and wc.specno LIKE '%{0}%' ", para("SpecNo"))
        End If
        If para.ContainsKey("BeginDate") Then
            strSQL += String.Format(" and wc.finishdate>=to_date('{0}','yyyy/mm/dd') ", para("BeginDate"))
        End If
        If para.ContainsKey("EndDate") Then
            strSQL += String.Format(" and wc.finishdate<=to_date('{0}','yyyy/mm/dd') ", para("EndDate"))
        End If
        strSQL += "  ORDER BY wc.productname,wc.specno,wc.batch"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSQL.ToString
        '主数据进行分页
        GetAppraisalItem = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    Function GetOperationTransferData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSRoutineTestIBusiness.GetOperationTransferData
        Dim strSql As String = <string>
                                    SELECT E.FULLNAME OPERATEFULLNAME,
                                     NVL2(mtd.managerflowoperationdetailid,'是','否') istransfer,
                                     NVL2(MT.LOCALTIME, NULL, MT.TIMENUM || '次/' || MT.CYCLE) FREQUENCY,                                          
                                     MT.MANAGEROPERATIONFLOWID,
                                     DECODE(MT.type,'0','标准任务','1','临时任务','2','鼓励性任务','3','S','4','Q','5','C','6','D','7','P','其他') CONTENTTYPE,
                                     MT.CREATEEMPLOYEEID,
                                     MT.CREATEDATE,
                                     MT.OPERATIONCONTENT,
                                     MT.LOCALTIME,
                                     MT.TIMENUM,
                                     MT.OPERATEEMPLOYEE,
                                     MT.TYPE,
                                     MT.CYCLE
                              FROM MANAGEROPERATIONFLOW MT
                              LEFT JOIN MANAGERFLOWOPERATIONDETAIL mtd ON mt.manageroperationflowid=mtd.manageroperationflowid AND to_char(mtd.receivedate,'yyyymmdd')=to_char(SYSDATE,'yyyymmdd') AND mtd.isuse=0
                                  LEFT JOIN EMPLOYEE E
                                  ON E.EMPLOYEEID = MT.OPERATEEMPLOYEE                                       
                                  WHERE 1 = 1
                        </string>
        If para.ContainsKey("operateemployee") Then
            strSql += "  AND nvl(mtd.receiveemployeeid,mt.operateemployee)='" + para("operateemployee") + "'"
        End If

        If para.ContainsKey("operationcontent") Then
            strSql += " and mt.operationcontent like '%" + para("operationcontent") + "%'"
        End If

        If para.ContainsKey("type") Then
            strSql += " and mt.type=" + para("type")
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString

        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    Function InsertTransfer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSRoutineTestIBusiness.InsertTransfer
        InsertTransfer = False
        Dim SQLStringList As New ArrayList
        Dim strsql As String = String.Format(
            " update MANAGEROPERATIONFLOWCOMPLETE set createemployeeid='{0}'where manageroperationflowcompleteid='{1}' ",
            para("ReceiveEmp"), para("MainFlowId"))
        SQLStringList.Add(strsql)
        strsql =
           " insert into MANAGERFLOWOPERATIONDETAIL(manageroperationflowcompleteid,managerflowoperationdetailid,transferemployeeid,transferdate,receiveemployeeid,receivedate) "
        strsql += String.Format(" values ('{0}','{1}','{2}',sysdate,'{3}',sysdate)", para("MainFlowId"), para("Id"), para("TransferEmp"), para("ReceiveEmp"))
        SQLStringList.Add(strsql)
        DatabaseHelper.ExecuteSqlTran(SQLStringList)
        Return True
    End Function

    Function ReturnTransfer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSRoutineTestIBusiness.ReturnTransfer
        ReturnTransfer = False
        Dim strsql As String = String.Format(
            " update MANAGERFLOWOPERATIONDETAIL set returnemployeeid='{0}',returndate=sysdate,isuse=1 where managerflowoperationdetailid='{2}' ",
            para("ReturnEmp"), para("TransferFlowId"))
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function
End Class


